﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DA
{
    public class DonHangDA:BaseConnection
    {
        public SqlDataAdapter danhSachDonHangAdapter { set; get; }
        public SqlDataAdapter theoDoiDonHangChungAdapter { set; get; }
        public DonHangDA()
        {
           
        }
            
        public DataSet timKiemDonHang(int timKiemTheo, String strchuoiTimKiem)
        {
            DataSet ds = new DataSet();
            String sqlStatement = "";
            if (timKiemTheo == 1)
            {
                sqlStatement = "SELECT MaDonHang, MaSanPham, SoLuong, QuyCachPallet";
                sqlStatement += " FROM DonHang";
                sqlStatement += " WHERE MaDonHang = '" + strchuoiTimKiem + "'";

                openConnect();

                SqlCommand cmd = new SqlCommand(sqlStatement, con);
                danhSachDonHangAdapter = new SqlDataAdapter(cmd);
                danhSachDonHangAdapter.SelectCommand = cmd;
                SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(danhSachDonHangAdapter);
                danhSachDonHangAdapter.Fill(ds, "DonHang");
                closeConnect();
            }
       
            
            return ds;         
        }


        public DataSet tiKiemDonHangTheoMaKhachHang(string maKhachHang)
        {
            DataSet ds = new DataSet();
            
            return ds;
        }

        public DataSet sanXuatBanThanhPham(string maDonHang)
        {
            openConnect();
            String sqlStm = "SELECT	bom.MaBanThanhPham AS MaBanThanhPham,";            
            sqlStm += "		CAST(SUM((dh.SoLuong*bom.KhoiLuong)) AS DECIMAL(10,2))			    			AS 'SoLuongCanSanXuat', ";
            sqlStm += "		CAST(SUM(btp.SoLuongTonKho) AS DECIMAL(10,2))								    AS 'SoLuongHienCo',";
            sqlStm += "		CAST(SUM(dh.SoLuong*bom.KhoiLuong - btp.SoLuongTonKho) AS DECIMAL(10,2))	    AS 'SoLuongCanNghien',";
            sqlStm += "		(SELECT TOP 1 DonHang.NgaySanXuat FROM DonHang WHERE DonHang.MaDonHang = @maDonHang) AS 'NgaySanXuat' ";
            sqlStm += "FROM DonHang dh ";
            sqlStm += "INNER JOIN BOM bom ON(dh.MaSanPham = bom.MaSanPham) ";
            sqlStm += "INNER JOIN BanThanhPham btp ON(btp.MaBanThanhPham = bom.MaBanThanhPham) ";
            sqlStm += "WHERE dh.MaDonHang = @maDonHang ";
            sqlStm += "GROUP BY bom.MaBanThanhPham";

            SqlCommand cmd = new SqlCommand(sqlStm, con);
            cmd.Parameters.Add("@maDonHang", SqlDbType.VarChar).Value = maDonHang;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            closeConnect();
            return ds;           
         }

        public DataSet laySanPhamTheoDonHang(String maDonHang)
        {
            String sqlStm = "SELECT MaSanPham FROM DonHang Where MaDonHang='" + maDonHang+"'";
            return layDuLieuTheoCauLenhSQL(sqlStm);
        }
         public DataSet theoDoiDonHangChung()
        {
            openConnect();
            String sqlStm = "SELECT";        
            sqlStm += " ttdh.MaDonHang,";
            sqlStm += " kh.TenKhachHang,";
            sqlStm += " (SELECT SUM(dh.SoLuong) FROM DonHang dh WHERE dh.MaDonHang = ttdh.MaDonHang) AS SoLuong,";
            sqlStm += " (SELECT CONVERT(VARCHAR(10), ttdh.NgayNhan, 103)) AS NgayNhan,";
            sqlStm += " (SELECT CONVERT(VARCHAR(10), ttdh.NgayGiao, 103)) AS NgayGiao,";
            sqlStm += " (SELECT SUM(tdcn.SanLuong) FROM TheoDoiCongNhan tdcn WHERE tdcn.MaDonHang = ttdh.MaDonHang) AS TinhTrang,";
            sqlStm += " (SELECT SUM(tdcn.SoLoi) FROM TheoDoiCongNhan tdcn WHERE tdcn.MaDonHang = ttdh.MaDonHang) AS TongLoi";
            sqlStm += " FROM ThongTinDonHang ttdh";
            sqlStm += " INNER JOIN KhachHang kh ON (ttdh.MaKhachHang = kh.MaKhachHang)";

        

            SqlCommand cmd = new SqlCommand(sqlStm, con);
            theoDoiDonHangChungAdapter = new SqlDataAdapter(cmd);
            //theoDoiDonHangChungAdapter.SelectCommand = cmd;
            //SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(theoDoiDonHangChungAdapter);
            DataSet dsTheoDoiDonHangChung = new DataSet();
            theoDoiDonHangChungAdapter.Fill(dsTheoDoiDonHangChung);
            closeConnect();
            return dsTheoDoiDonHangChung;
        }
         public DataSet theoDoiDonHangChiTiet(string maDonHang)
         {
             openConnect();
             string sqlStm = "SELECT DISTINCT";
             sqlStm += " dh.MaSanPham, ";
             sqlStm += " dh.SoLuong,  ";
             sqlStm += " (SELECT SUM(tempTDCN.SanLuong) ";
             sqlStm += " FROM ";
             sqlStm += " (SELECT DISTINCT ";
             sqlStm += " MaCongNhan, ";
             sqlStm += " SanLuong ";
             sqlStm += " FROM TheoDoiCongNhan tdcn ";
             sqlStm += " WHERE tdcn.MaSanPham = dh.MaSanPham ";
             sqlStm += " ) tempTDCN) AS SanLuongSanXuat, ";
             sqlStm += " SUM(tdcn.SoLoi) OVER(PARTITION BY CHECKSUM(dh.MaDonHang, dh.MaSanPham) ) AS TongSoLoi ";
             sqlStm += " FROM DonHang dh ";
             sqlStm += " INNER JOIN TheoDoiCongNhan tdcn ON (dh.MaDonHang = tdcn.MaDonHang AND dh.MaSanPham = tdcn.MaSanPham) ";
             sqlStm += " WHERE dh.MaDonHang='" + maDonHang + "'";

             SqlCommand cmd = new SqlCommand(sqlStm, con);
             theoDoiDonHangChungAdapter = new SqlDataAdapter(cmd);
             //theoDoiDonHangChungAdapter.SelectCommand = cmd;
             //SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(theoDoiDonHangChungAdapter);
             DataSet dsTheoDoiDonHangChung = new DataSet();
             theoDoiDonHangChungAdapter.Fill(dsTheoDoiDonHangChung);
             closeConnect();
             return dsTheoDoiDonHangChung;


         }


    }
}
